﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OracleClient;
using Pms.Utils;
using Biblioteca.Utils;
using System.ComponentModel;

namespace SERIX.Classes
{
    internal struct StructExecucao
    {
        public int idt_Ind;        
        public int Idt_AIS;        
        public int? Idt_Tpo_Med2;
        public decimal? valor2;
        public int? Idt_Tpo_Med3;
        public decimal? valor3;        
        public override bool Equals(object obj)
        {
            if (obj is StructProgramacao)
            {
                StructProgramacao prg = (StructProgramacao)obj;
                if (prg.idt_Ind == this.idt_Ind
                    && prg.Idt_Tpo_Med2 == this.Idt_Tpo_Med2
                    && prg.Idt_AIS == this.Idt_AIS
                    //&& prg.ordem_Mir == this.ordem_Mir
                    )
                    return true;
                //return base.Equals(obj);
            }
            return false;
        }
    }


    [System.ComponentModel.DataObject(true)]
    public class CExecucao
    {
        [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
        public static DataTable ObterBeneficarioOrganizacao(string valor, string login)
        {
            if (string.IsNullOrEmpty(valor))
                return new DataTable();
            string sql = @"select nom_benef nome, 'Benef' || idt_benef idt
from srx_beneficiarios
where cpf like :valor or nom_benef like :valor
union
select nom_org nome, 'Org' || idt_org idt from srx_organizacoes
where cnpj like :valor or nom_org like :valor";

            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = sql;
                command.Parameters.Add(new OracleParameter("valor", OracleType.VarChar));
                valor = "%" + valor + "%";
                command.Parameters["valor"].Value = valor.ToUpper();
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }


        [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
        public static DataTable ObterPropriedade(string idt_benef, string login)
        {
            if (string.IsNullOrEmpty(idt_benef))
                return new DataTable();

            string sql = @"select Nome_Prop, idt_Prop from srx_Propriedades
where idt_benef = :idt_benef";

            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = sql;
                command.Parameters.Add(new OracleParameter("idt_benef", OracleType.VarChar));
                command.Parameters["idt_benef"].Value = idt_benef;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable ObterMunicipios(int idt_Tec, string login)
        {
            string sql = @"select mun.cod_IBGE || ' - ' || mun.nome_mun NOME_MUN,
mun.idt_mun from srx_tecnicos tec
  join srx_lotacoes_tecnicos lotTec on lottec.idt_tec = tec.idt_tec
  join srx_municipios mun on mun.idt_mun = lottec.idt_mun
where lottec.sta_lotacao = 'A' AND tec.idt_tec = :idt_tec

union

SELECT U.COD_IBGE ||' - '|| U.NOME_MUN NOME_MUN, U.IDT_MUN FROM SRX_MUNICIPIOS U
                            join SRX_USUARIOS_MUNICIPIOS S ON (U.IDT_MUN = S.IDT_MUN) 
                                AND (S.STATUS = 'A') AND (S.IDT_TEC = :IDT_TEC)";
            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = sql;

                command.Parameters.Add(new OracleParameter("idt_tec", OracleType.Number));

                command.Parameters["idt_tec"].Value = idt_Tec;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable ObterExecucao(string idt_prog, string idt_acao, string ano_exec, string mes_exec, string idt_tec, string idt_benef, string idt_org, string idt_prop, string login)
        {            
            string idt_Mun = string.Empty;
            if (string.IsNullOrEmpty(idt_tec) || string.IsNullOrEmpty(ano_exec)
            || string.IsNullOrEmpty(mes_exec) || string.IsNullOrEmpty(idt_prog) ||
            string.IsNullOrEmpty(idt_acao) ||
            (string.IsNullOrEmpty(idt_benef) && string.IsNullOrEmpty(idt_org))
                )
                return new DataTable();
            string sql = @"
select ais.idt_AIS, ais.Idt_Prog, Ano_Ais, ais.idt_acao, ais.Idt_Ind, ind.dsc_ind,
  valor_Exec2, valor_Exec3,   
  nvl(Idt_TPo_MEd2, (select Idt_TPo_MEd from srx_medidas_indicadores medInd
        where medind.idt_ind = ind.idt_ind and medind.ordem_mir = 2)) Idt_TPo_MEd2,
  nvl(Idt_TPo_MEd3, (select Idt_TPo_MEd from srx_medidas_indicadores medInd
        where medind.idt_ind = ind.idt_ind and medind.ordem_mir = 3)) Idt_TPo_MEd3  
  from srx_indicadores ind
  join srx_acoes_indicadores ais on ais.idt_ind = ind.idt_ind
  join srx_programas_municipios pms on pms.idt_prog = ais.idt_prog and pms.pms_ano = ais.ano_ais  
  left join srx_execucao exec on exec.idt_AIS = ais.idt_ais and exec.idt_mun = pms.idt_mun 
where ais.Idt_Prog = :Idt_Prog and Ano_Ais = :Ano_Ais 
 and ais.idt_acao = :idt_acao and pms.idt_mun = :idt_mun
 and (idt_tec is null or (not idt_tec is null and idt_Tec = :idt_tec and mes_exec = :mes_exec and exec.{0} = :atendido {1}))";

            OracleCommand command = new OracleCommand();
            try
            {
                string filtroPropriedade = string.Empty;
                if (!string.IsNullOrEmpty(idt_prop))
                {
                    idt_Mun = CPropriedades.ObterIdt_Mun(idt_prop, login);
                    filtroPropriedade = " and idt_Prop = :idt_Prop";
                    command.Parameters.Add(new OracleParameter("idt_Prop", OracleType.Number));
                    command.Parameters["idt_Prop"].Value = idt_prop;
                }

                string atendido;
                if (!string.IsNullOrEmpty(idt_benef))
                {
                    sql = string.Format(sql, "idt_benef", filtroPropriedade);
                    atendido = idt_benef;
                    if (string.IsNullOrEmpty(idt_Mun))
                        idt_Mun = CBeneficiarios.ObterIdt_Mun(idt_benef, login);
                }
                else
                {
                    sql = string.Format(sql, "idt_org", filtroPropriedade);
                    atendido = idt_org;
                    if (string.IsNullOrEmpty(idt_Mun))
                        idt_Mun = COrganizacoes.ObterIdt_Mun(idt_org, login);
                }


                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = sql;

                command.Parameters.Add(new OracleParameter("Idt_Prog", OracleType.Number));
                command.Parameters.Add(new OracleParameter("Ano_Ais", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_Mun", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_Acao", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_tec", OracleType.Number));
                command.Parameters.Add(new OracleParameter("mes_exec", OracleType.Number));
                command.Parameters.Add(new OracleParameter("atendido", OracleType.Number));

                command.Parameters["Idt_Prog"].Value = idt_prog;
                command.Parameters["Ano_Ais"].Value = ano_exec;
                command.Parameters["idt_Mun"].Value = idt_Mun;
                command.Parameters["idt_Acao"].Value = idt_acao;
                command.Parameters["idt_tec"].Value = idt_tec;
                command.Parameters["mes_exec"].Value = mes_exec;
                command.Parameters["atendido"].Value = atendido;



                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        public static string ObterIdt_Mun(string idt_prop, string idt_benef, string idt_org, string login)
        {
            string idt_Mun;
            if (!string.IsNullOrEmpty(idt_prop))
            {
                idt_Mun = CPropriedades.ObterIdt_Mun(idt_prop, login);
            }
            else
            {
                if (!string.IsNullOrEmpty(idt_benef))
                    idt_Mun = CBeneficiarios.ObterIdt_Mun(idt_benef, login);
                else
                    idt_Mun = COrganizacoes.ObterIdt_Mun(idt_org, login);
            }
            return idt_Mun;
        }

        internal static void GravarExecucao(List<StructExecucao> execucao, string idt_tec, string idt_benef, string idt_org,
            string mes_exec, string idt_prop, string login)
        {
            string idt_Mun = ObterIdt_Mun(idt_prop, idt_benef, idt_org, login);
            string SQL_DELETE = @"DELETE FROM Serix.SRX_EXECUCAO
                                   WHERE idt_ais = :idt_ais and MES_EXEC = :MES_EXEC and IDT_TEC = :IDT_TEC and IDT_MUN = :IDT_MUN ";

            string SQL_INSERT = @"insert into Serix.SRX_EXECUCAO 
  (idt_EXEC, IDT_BENEF, IDT_MUN, IDT_ORG, MES_EXEC, IDT_TEC, IDT_PROP, idt_ais, idt_Tpo_Med2, valor_Exec2, idt_Tpo_Med3, valor_Exec3)
values
((select  nvl(max(idt_Exec),1) from Serix.SRX_EXECUCAO)+1
  , :IDT_BENEF, :IDT_MUN, :IDT_ORG, :MES_EXEC, :IDT_TEC, :IDT_PROP, :idt_ais
  , :idt_Tpo_Med2, :valor_Exec2
  , :idt_Tpo_Med3, :valor_Exec3
)";

            OracleCommand command = new OracleCommand();
            OracleTransaction trans;
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;


                command.Parameters.Add(new OracleParameter("IDT_MUN", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_ORG", OracleType.Number));
                command.Parameters.Add(new OracleParameter("MES_EXEC", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_TEC", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_PROP", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_AIS", OracleType.Number));

                if (!string.IsNullOrEmpty(idt_benef))
                {
                    SQL_DELETE += "and IDT_BENEF = :IDT_BENEF ";
                    command.Parameters.Add(new OracleParameter("IDT_BENEF", OracleType.Number));

                    command.Parameters["IDT_BENEF"].Value = idt_benef;
                }
                if (!string.IsNullOrEmpty(idt_org))
                {
                    SQL_DELETE += "and IDT_ORG = :IDT_ORG ";
                    command.Parameters.Add(new OracleParameter("IDT_ORG", OracleType.Number));

                    command.Parameters["IDT_ORG"].Value = idt_org;
                }
                if (!string.IsNullOrEmpty(idt_prop))
                {
                    SQL_DELETE += "and IDT_PROP = :IDT_PROP ";
                    command.Parameters.Add(new OracleParameter("IDT_PROP", OracleType.Number));
                    command.Parameters["IDT_PROP"].Value = idt_prop;
                }
                command.Parameters["IDT_MUN"].Value = idt_Mun;
                command.Parameters["MES_EXEC"].Value = mes_exec;
                command.Parameters["IDT_TEC"].Value = idt_tec;
                command.CommandText = SQL_DELETE;

                foreach (StructExecucao exec in execucao)
                {
                    command.Parameters["idt_AIS"].Value = exec.Idt_AIS;
                    command.ExecuteNonQuery();
                }

                command.ExecuteNonQuery();
                command.Parameters.Clear();

                command.CommandText = SQL_INSERT;

                command.Parameters.Add(new OracleParameter("IDT_BENEF", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_MUN", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_ORG", OracleType.Number));
                command.Parameters.Add(new OracleParameter("MES_EXEC", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_TEC", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_PROP", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_AIS", OracleType.Number));

                command.Parameters.Add(new OracleParameter("idt_Tpo_Med2", OracleType.Number));
                command.Parameters.Add(new OracleParameter("valor_Exec2", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_Tpo_Med3", OracleType.Number));
                command.Parameters.Add(new OracleParameter("valor_Exec3", OracleType.Number));

                command.Parameters["IDT_BENEF"].Value = string.IsNullOrEmpty(idt_benef) ? (object)DBNull.Value : (object)idt_benef;
                command.Parameters["IDT_MUN"].Value = idt_Mun;
                command.Parameters["IDT_ORG"].Value = string.IsNullOrEmpty(idt_org) ? (object)DBNull.Value : (object)idt_org;
                command.Parameters["MES_EXEC"].Value = mes_exec;
                command.Parameters["IDT_TEC"].Value = idt_tec;
                command.Parameters["IDT_PROP"].Value = string.IsNullOrEmpty(idt_prop) ? (object)DBNull.Value : (object)idt_prop;

                foreach (StructExecucao exec in execucao)
                {
                    command.Parameters["IDT_AIS"].Value = exec.Idt_AIS;

                    if (exec.Idt_Tpo_Med2 == null || exec.valor2 == null)
                        continue;
                    command.Parameters["idt_Tpo_Med2"].Value = exec.Idt_Tpo_Med2;
                    command.Parameters["valor_Exec2"].Value = exec.valor2;
                    if (exec.Idt_Tpo_Med3 == null)
                    {
                        command.Parameters["idt_Tpo_Med3"].Value = DBNull.Value;// ;
                        command.Parameters["valor_Exec3"].Value = DBNull.Value;// programa.valor2;
                    }
                    else
                    {
                        command.Parameters["idt_Tpo_Med3"].Value = exec.Idt_Tpo_Med2;
                        command.Parameters["valor_Exec3"].Value = exec.valor2;
                    }

                    command.ExecuteNonQuery();

                }

                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command.Connection != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }
    }
}
